package com.projectbox.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import com.projectbox.dao.ResourceDao;
import com.projectbox.model.Folder;
import com.projectbox.model.Resource;

public class JdbcResourceDaoImpl implements ResourceDao {

	private Logger logger = LoggerFactory.getLogger(getClass());

	private SimpleJdbcTemplate simpleJdbcTemplate;
	private SimpleJdbcInsert insertTemplate;
	private SimpleJdbcInsert insertFolderTemplate;

	public void setDataSource(DataSource dataSource) {
		this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
		this.insertTemplate = new SimpleJdbcInsert(dataSource).withTableName(
				"resources").usingGeneratedKeyColumns("id");
		this.insertFolderTemplate = new SimpleJdbcInsert(dataSource)
				.withTableName("folder").usingGeneratedKeyColumns("id");
	}

	public Resource getResourceById(Long id) {
		logger.debug("resource id " + id);

		String sql = "select * from resources where id = ?";
		Resource resource = null;
		try {
			resource = this.simpleJdbcTemplate.queryForObject(sql,
					new ResourceMapper(), id);

			logger.debug("Get " + resource + " from DB");
		} catch (Exception e) {
			resource = null;
		}

		return resource;
	}

	public void insert(Resource resource) {
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("name", resource.getName());
		parameters.put("description", resource.getDescription());
		parameters.put("type", resource.getType());
		parameters.put("path", resource.getPath());
		parameters.put("time_created", resource.getTimeCreated());
		parameters.put("time_modified", resource.getTimeModified());
		parameters.put("uid", resource.getUid());
		parameters.put("pid", resource.getPid());
		if (resource.getFid() != 0) {
			logger.debug("fid is not null");
			parameters.put("fid", resource.getFid());
		} else {
			logger.debug("fid is null");
		}
		parameters.put("lastModId", resource.getLastModId());

		Number newId = insertTemplate.executeAndReturnKey(parameters);
		resource.setId(newId.longValue());
	}

	public void delete(Long id) {
		String sql = "delete from resources where id=?";
		simpleJdbcTemplate.update(sql, id);
	}

	public void renameResource(Long id, String name, String type) {
		
		String sql = "update resources set name=?, type=? where id=?";
		simpleJdbcTemplate.update(sql, name, type, id);
		long time = System.currentTimeMillis();
		updateLastModify(id,time);
		
	}

	public void updateLastModify(Long id, long modifyTime) {
		String sql = "update resources set time_modified=? where id=?";
		simpleJdbcTemplate.update(sql, modifyTime, id);
	}

	public List<Resource> getAllProjectResources(Long pid) {
		String sql = "select * from resources where pid=? order by name";
		List<Resource> resources = simpleJdbcTemplate.query(sql,
				new ResourceMapper(), pid);

		return resources;
	}
	
	public List<Resource> getProjectResources(Long pid) {
		String sql = "select * from resources where fid is null and pid=? order by name";
		List<Resource> resources = simpleJdbcTemplate.query(sql,
				new ResourceMapper(), pid);

		return resources;
	}

	public Resource checkResourceName(Long fid, String newName) {
		Resource resource = null;
		String sql = "";
		try {
			if (fid == 0) {
				sql = "select * from resources where name=? and fid is null";
				resource = simpleJdbcTemplate.queryForObject(sql,
						new ResourceMapper(), newName);
			} else {
				sql = "select * from resources where name=? and fid=?";
				resource = simpleJdbcTemplate.queryForObject(sql,
						new ResourceMapper(), newName, fid);
			}
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
		return resource;
	}

	public List<Resource> getProjectResourcesByUserId(Long uid, Long pid) {
		String sql = "select * from resources where uid=? and pid=?";
		List<Resource> resources = simpleJdbcTemplate.query(sql,
				new ResourceMapper(), uid, pid);

		logger.debug("" + resources);
		return resources;
	}

	public void insertFolder(Folder folder) {
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("name", folder.getName());
		parameters.put("preId", folder.getPreId());
		parameters.put("pid", folder.getPid());
		Number newId = insertFolderTemplate.executeAndReturnKey(parameters);
		folder.setId(newId.longValue());
	}

	public void deleteFolder(Long id) {
		String sql = "delete from folder where id=?";
		simpleJdbcTemplate.update(sql, id);
	}

	public void deleteResourcesInFolder(Long fid) {
		// TODO Auto-generated method stub
		String sql = "delete from resources where fid=?";
		simpleJdbcTemplate.update(sql, fid);
	}

	public void renameFolder(Long id, String name) {
		String sql = "update folder set name=? where id=?";
		simpleJdbcTemplate.update(sql, name, id);
	}

	public Folder getFolderById(Long id) {
		logger.debug("folder id " + id);

		String sql = "select * from folder where id = ?";
		Folder folder = null;
		try {
			folder = this.simpleJdbcTemplate.queryForObject(sql,
					new FolderMapper(), id);
		} catch (Exception e) {
			folder = null;
		}
		
		return folder;
	}

	public List<Resource> getResourcesByFolder(Long fid) {
		String sql = "select * from resources where fid=? order by name";
		List<Resource> resources = simpleJdbcTemplate.query(sql,
				new ResourceMapper(), fid);
		return resources;
	}

	public List<Folder> getSubfolders(Long preId) {
		String sql = "select * from folder where preId=? order by name";
		List<Folder> folders = simpleJdbcTemplate.query(sql,
				new FolderMapper(), preId);
		return folders;
	}

	public List<Folder> getSubfoldersByPid(Long pid) {
		String sql = "select * from folder where preId=0 and pid=? order by name";
		List<Folder> folders = simpleJdbcTemplate.query(sql,
				new FolderMapper(), pid);
		return folders;
	}

	public Folder checkFolderName(Long preId, String newName) {
		Folder folder = null;
		String sql = "select * from folder where name=? and preId=?";
		try {
			folder = simpleJdbcTemplate.queryForObject(sql, new FolderMapper(),
					newName, preId);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
		return folder;
	}

	private static final class ResourceMapper implements RowMapper<Resource> {
		public Resource mapRow(ResultSet rs, int rowNum) throws SQLException {
			Resource resource = new Resource();
			resource.setId(rs.getLong("id"));
			resource.setName(rs.getString("name"));
			resource.setDescription(rs.getString("description"));
			resource.setType(rs.getString("type"));
			resource.setPath(rs.getString("path"));
			resource.setTimeCreated(rs.getLong("time_created"));
			resource.setTimeModified(rs.getLong("time_modified"));
			resource.setUid(rs.getLong("uid"));
			resource.setPid(rs.getLong("pid"));
			resource.setFid(rs.getLong("fid"));
			resource.setLastModId(rs.getLong("lastModId"));
			return resource;
		}
	}

	private static final class FolderMapper implements RowMapper<Folder> {
		public Folder mapRow(ResultSet rs, int rowNum) throws SQLException {
			Folder folder = new Folder();
			folder.setId(rs.getLong("id"));
			folder.setName(rs.getString("name"));
			folder.setPreId(rs.getLong("preId"));
			folder.setPid(rs.getLong("pid"));
			return folder;
		}
	}

	public List<Resource> getResourcesByUserId(Long uid) {
		String sql = "select * from resources where uid=?";
		List<Resource> resources = simpleJdbcTemplate.query(sql,
				new ResourceMapper(), uid);

		logger.debug("" + resources);
		return resources;
	}
	
	public List<Resource> getLatestResources(Long uid){
		
		String sql = "select * from resources where uid!=" + uid + " and (pid IN (select id from projects p where p.mid="+uid+") or pid IN (select pid from accesses where uid="+uid+" and status='granted')) order by time_modified";
		List<Resource> resources = simpleJdbcTemplate.query(sql,new ResourceMapper());


		logger.debug("" + resources);
		return resources;
	}
}
